#
import pymysql
from typing import Tuple, List
import json
import os
from datetime import datetime
# from DBUtils.PooledDB import PooledDB
from dbutils.pooled_db import PooledDB
import logging

# 配置日志记录
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

# 数据库配置（从环境变量获取）
DB_HOST = os.getenv('DB_HOST', 'localhost')
DB_USER = os.getenv('DB_USER', 'gyb')
DB_PASSWORD = os.getenv('DB_PASSWORD', 'Gyb2025')
DB_NAME = os.getenv('DB_NAME', 'GybDb')
DB_PORT = int(os.getenv('DB_PORT', 3306))

# 创建连接池
pool = PooledDB(
    creator=pymysql,  # 使用的数据库驱动
    mincached=2,      # 启动时创建的闲置连接数量
    maxcached=5,      # 连接池中空闲连接的最大数量
    maxconnections=10,  # 连接池允许的最大连接数
    blocking=True,    # 当连接池达到最大数量时，新请求是否阻塞等待
    host=DB_HOST,
    port=DB_PORT,
    user=DB_USER,
    password=DB_PASSWORD,
    database=DB_NAME,
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

def get_db_connection():
    """从连接池获取数据库连接"""
    try:
        conn = pool.connection()
        logger.info("成功从连接池获取数据库连接")
        return conn
    except Exception as e:
        logger.error(f"从连接池获取连接失败: {str(e)}")
        raise

class MysqlEngine(object):
    def __init__(self):
        self.name = 'dbs.mysql_engine.MysqlEngine'
    
    @staticmethod
    def add_rec(sql:str, params) -> int:
        pk_id = -1
        # 获取数据库连接
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql, params)
                pk_id = cursor.lastrowid
                print(f'pk_id: {pk_id};')
                # 提交事务
                conn.commit()
        except pymysql.err.IntegrityError as e:
            # 唯一约束冲突（如重复邮箱）
            conn.rollback()
            return -1
        except Exception as e:
            conn.rollback()
            logger.error(f"数据库操作失败: {str(e)}")
            return -1
        finally:
            # 重要：将连接归还到连接池
            conn.close()
        return pk_id
    
    @staticmethod
    def query(sql:str, params) -> List:
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql, params)
                recs = cursor.fetchall()
                return recs  # 使用 jsonify
        except Exception as e:
            return None # jsonify({'error': f'数据库错误: {str(e)}'}), 500  # 使用 jsonify
        finally:
            conn.close()
    





    @staticmethod
    def add_qstn():
        qstn_title = 't1'
        notes = 'n2'
        # 获取数据库连接
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'INSERT INTO z_qstn(qstn_title, notes) VALUES(%s, %s)'
                cursor.execute(sql, (qstn_title, notes))
                qstn_id = cursor.lastrowid
                print(f'qstn_id: {qstn_id};')
                # 提交事务
                conn.commit()
        except pymysql.err.IntegrityError as e:
            # 唯一约束冲突（如重复邮箱）
            conn.rollback()
            return{'error': '邮箱已存在'}
        except Exception as e:
            conn.rollback()
            logger.error(f"数据库操作失败: {str(e)}")
            return {'error': f'数据库错误: {str(e)}'}
        finally:
            # 重要：将连接归还到连接池
            conn.close()

if '__main__' == __name__:
    MysqlEngine.add_qstn()